package com.ch.fastbuilder.model;

import java.sql.Connection;
import java.text.MessageFormat;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.UUID;

import javax.sql.DataSource;

import org.springframework.jdbc.datasource.DataSourceUtils;

import com.ch.fastbuilder.common.GenerationType;
import com.ch.fastbuilder.common.PageResult;
import com.ch.fastbuilder.common.SpringContextUtil;
import com.ch.fastbuilder.jdbc.JdbcQuery;
import com.ch.fastbuilder.jdbc.JdbcQueryImpl;

public class Model extends BuilderModel {
	/**
	 * 
	 */
	private static final long serialVersionUID = 1L;

	public void create() {
		Create(this);
	}

	public void update() {
		Update(this);
	}

	public void delete() {
		Delete(this);
	}
	
	public static <T extends Model> T Get(Object id, Class<T> classType) {
		long startTime = System.currentTimeMillis();    //鑾峰彇寮�濮嬫椂闂�
		
		T model = null;
		Connection conn = getConnection();
		try {
			model = classType.newInstance();
			checkParams(model);
			
			String primaryKey = model.getPrimaryKey();
			String primaryKeyNamed = ":" + primaryKey;

			String sql = MessageFormat.format(getTemplateSQL, model.strColumns, model.getTableName(),
					primaryKey, primaryKeyNamed);

			Map<String, Object> params = new HashMap<String, Object>();
			params.put(primaryKey, id);

			JdbcQuery jdbcQuery = new JdbcQueryImpl(conn, sql, params);
			model = jdbcQuery.get(classType);
		} 
		catch (Exception e) {
			throw new RuntimeException(e.getMessage());
		}
		/*finally {
			try {
				if(isAutoCommit())
					conn.close();
			} catch (SQLException e) {
				throw new RuntimeException(e.getMessage());
			}
		}*/
		//事务交给spring来管理，无需手动控制事务及释放connection
		finally {}
		
		long endTime = System.currentTimeMillis();    
		System.out.println("Model.Get  spend time: " + (endTime - startTime) + "ms");

		return model;
	}
	
	public static void Create(Model model) {
		long startTime = System.currentTimeMillis();    
		
		Connection conn = getConnection();
		try {
			model.putAll(model.params);
			checkParams(model);
			
			 if(model.generationType == GenerationType.UUID) {
				 String id = model.getString(model.primaryKey);
                 if(!model.containsKey(model.primaryKey) || id == null || id.trim().length() == 0) {
                     model.set(model.primaryKey, UUID.randomUUID().toString());
                 }
             }
			
			StringBuffer strColumns = new StringBuffer();
			StringBuffer strParams = new StringBuffer();
			for (String key : model.keySet()) {
				if(!model.columns.contains(key)) continue;
				
				String nKey = formatColumnName(key);
				strColumns.append(nKey).append(",");
				strParams.append(":").append(key).append(",");
			}
	
			strColumns.deleteCharAt(strColumns.length() - 1);
			strParams.deleteCharAt(strParams.length() - 1);
	
			String sql = MessageFormat.format(insertTemplateSQL, model.getTableName(), strColumns.toString(),
					strParams.toString());
	
			JdbcQuery jdbcQuery = new JdbcQueryImpl(conn, sql, model);
			jdbcQuery.excuteUpdate();
			
			if(model.generationType == GenerationType.IDENTITY) {
				jdbcQuery.setSQLParamters(conn, selectAutoIdSQL);
				Long autoId = Long.parseLong(jdbcQuery.getSingleResult().toString());
				model.put(model.primaryKey, autoId);
			}
		} 
		catch (Exception e) {
			throw new RuntimeException(e.getMessage());
		}
		finally {}
		
		long endTime = System.currentTimeMillis();   
		System.out.println("Model.Create  spend time: " + (endTime - startTime) + "ms");
	}

	public static void Update(Model model) {
		long startTime = System.currentTimeMillis();    
		
		Connection conn = getConnection();
		try {
			model.putAll(model.params);
			checkParams(model);
			
			String primaryKey = model.getPrimaryKey();
			String primaryKeyNamed = ":" + primaryKey;
	
			StringBuffer strSets = new StringBuffer();
			for (String key : model.keySet()) {
				if (key.equals(primaryKey)) continue;
				if(!model.columns.contains(key)) continue;
				
				String nKey = formatColumnName(key);
				strSets.append(nKey).append("=").append(":").append(key).append(",");
			}
	
			strSets.deleteCharAt(strSets.length() - 1);
	
			String sql = MessageFormat.format(updateTemplateSQL, model.getTableName(), strSets.toString(),
					primaryKey, primaryKeyNamed);
	
			JdbcQuery jdbcQuery = new JdbcQueryImpl(conn, sql, model);
			jdbcQuery.excuteUpdate();
		} 
		catch (Exception e) {
			throw new RuntimeException(e.getMessage());
		}	
		finally {}
		
		long endTime = System.currentTimeMillis();   
		System.out.println("Model.Update  spend time: " + (endTime - startTime) + "ms");
	}

	public static void Delete(Model model) {
		long startTime = System.currentTimeMillis();   
		
		Connection conn = getConnection();
		try {
			checkParams(model);
			
			String tableName = model.getTableName();
			String primaryKey = model.getPrimaryKey();
			String primaryKeyNamed = ":" + primaryKey;
	
			String sql = MessageFormat.format(deleteTemplateSQL, tableName, primaryKey, primaryKeyNamed);
	
			JdbcQuery jdbcQuery = new JdbcQueryImpl(conn, sql, model);
			jdbcQuery.excuteUpdate();
		} 
		catch (Exception e) {
			throw new RuntimeException(e.getMessage());
		}
		finally {}
		
		long endTime = System.currentTimeMillis();    
		System.out.println("Model.Delete  spend time: " + (endTime - startTime) + "ms");
	}
	
	public static <T extends Model> void Delete(Object id, Class<T> classType) {
		long startTime = System.currentTimeMillis();    
		
		Connection conn = getConnection();
		try {
			T model = classType.newInstance();
			checkParams(model);
			
			String tableName = model.getTableName();
			String primaryKey = model.getPrimaryKey();
			String primaryKeyNamed = ":" + primaryKey;
			
			String sql = MessageFormat.format(deleteTemplateSQL, tableName, primaryKey, primaryKeyNamed);
	
			model.set(primaryKey, id);
			JdbcQuery jdbcQuery = new JdbcQueryImpl(conn, sql, model);
			jdbcQuery.excuteUpdate();
		} 
		catch (Exception e) {
			throw new RuntimeException(e.getMessage());
		}
		finally {}
		
		long endTime = System.currentTimeMillis();   
		System.out.println("Model.Delete  spend time: " + (endTime - startTime) + "ms");
	}

	public static <T extends Model> List<T> List(Class<T> classType) {
		Model model = new Model();
		List<T> modelList = model.list(classType);
		
		return modelList;
	}
	
	public <T extends Model> List<T> list(Class<T> classType) {
		long startTime = System.currentTimeMillis();    
		
		T model = null;
		List<T> modelList = null;
		Connection conn = getConnection();
		try {
			String querySQL = this.sql;
			if(this.sql == null || this.sql.trim().length() == 0) {
				model = classType.newInstance();
				querySQL = builderPageSQL(model, conn);
			} 

			JdbcQuery jdbcQuery = new JdbcQueryImpl(conn, querySQL, this.params);
			modelList = jdbcQuery.getModelList(classType);
		} 
		catch (Exception e) {
			throw new RuntimeException(e.getMessage());
		}
		finally {}
		
		long endTime = System.currentTimeMillis();   
		System.out.println("Model.list  spend time: " + (endTime - startTime) + "ms");

		return modelList;
	}
	
	public <T extends Model> T get(Class<T> classType) {
		long startTime = System.currentTimeMillis();    
		
		T model = null;
		Connection conn = getConnection();
		
		try {
			String querySQL = this.sql;
			if(this.sql == null || this.sql.trim().length() == 0) {
				T instance = classType.newInstance();
				querySQL = builderPageSQL(instance, conn);
			} 
			
			JdbcQuery jdbcQuery = new JdbcQueryImpl(conn, querySQL, this.params);
			
			List<T> modelList = jdbcQuery.getModelList(classType);
			if(modelList.size() > 0) {
				model = modelList.get(0);
			}
		} 
		catch (Exception e) {
			throw new RuntimeException(e.getMessage());
		}
		finally {}
		
		long endTime = System.currentTimeMillis();   
		System.out.println("Model.count  spend time: " + (endTime - startTime) + "ms");

		return model;
	}
	
	public static <T extends Model> int Count(Class<T> classType) {
		Model model = new Model();
		return model.count(classType);
	}
	
	public <T extends Model> int count(Class<T> classType) { 
		long startTime = System.currentTimeMillis();   
		
		int count = 0;
		Connection conn = getConnection();
		try {
			T model = classType.newInstance();
			
			String countSQL = this.sql;
			if(this.sql == null || this.sql.trim().length() == 0) {
				countSQL = builderCountSQL(model);
			} 

			JdbcQuery jdbcQuery = new JdbcQueryImpl(conn, countSQL, this.params);
			count = jdbcQuery.getCount();
		} 
		catch (Exception e) {
			throw new RuntimeException(e.getMessage());
		}
		finally {}
		
		long endTime = System.currentTimeMillis();    //spend time end
		System.out.println("Model.count spend time: " + (endTime - startTime) + "ms");

		return count;
	}
	
	public int count() { 
		long startTime = System.currentTimeMillis();    //spend time begin
		
		int count = 0;
		Connection conn = getConnection();
		try {
			if(this.sql == null || this.sql.trim().length() == 0) {
				throw new RuntimeException("SQL is null or empty!");
			}
			
			JdbcQuery jdbcQuery = new JdbcQueryImpl(conn, this.sql, this.params);
			count = jdbcQuery.getCount();
		} 
		catch (Exception e) {
			throw new RuntimeException(e.getMessage());
		}
		finally {}
		
		long endTime = System.currentTimeMillis();    
		System.out.println("Model.count spend time: " + (endTime - startTime) + "ms");

		return count;
	}
	
	public void excute() {
		long startTime = System.currentTimeMillis(); 
		
		Connection conn = getConnection();
		try {
			if(this.sql == null || this.sql.trim().length() == 0) {
				throw new RuntimeException("SQL is null or empty!");
			}
			
			JdbcQuery jdbcQuery = new JdbcQueryImpl(conn, this.sql, this.params);
			jdbcQuery.excuteUpdate();
		} 
		catch (Exception e) {
			throw new RuntimeException(e.getMessage());
		}
		finally {}
		
		long endTime = System.currentTimeMillis();    //鑾峰彇缁撴潫鏃堕棿
		System.out.println("Model.excute spend time: " + (endTime - startTime) + "ms");
	}
	
	public static <T extends Model> PageResult FindPage(Class<T> classType) {
		Model model = new Model();
		return model.findPage(classType);
	}
	
	public <T extends Model> PageResult findPage(Class<T> classType) { 
		long startTime = System.currentTimeMillis();    //鑾峰彇寮�濮嬫椂闂�
		
		PageResult pageResult = new PageResult();
		Connection conn = getConnection();
		try {
			T model = classType.newInstance();
			
			String countSQL = builderCountSQL(model);
			String querySQL = builderPageSQL(model, conn);

			JdbcQuery jdbcQuery = new JdbcQueryImpl(conn, countSQL, this.params);
			int count = jdbcQuery.getCount();
			
			jdbcQuery.setSQLParamters(conn, querySQL, this.params);
			List<T> modelList = jdbcQuery.getModelList(classType);
			
			pageResult.setTotal(count);
			pageResult.setData(modelList);
		} 
		catch (Exception e) {
			throw new RuntimeException(e.getMessage());
		}
		finally {}
		
		long endTime = System.currentTimeMillis();    
		System.out.println("Model.findPage spend time: " + (endTime - startTime) + "ms");
		
		return pageResult;
	}
	
	private <T extends Model> String builderCountSQL(T model) {
		if(this.sql != null && this.sql.trim().length() > 0) {
			return this.sql;
		}
		
		if(this.strColumns == null || this.strColumns.trim().length() == 0) 
			this.setColumns(model.strColumns);
		
		if(this.tableName == null || this.tableName.trim().length() == 0) 
			this.setTableName(model.getTableName());
		
		StringBuffer strJoinWhere = new StringBuffer();
		if(this.getJoin() != null && this.getJoin().trim().length() > 0) {
			strJoinWhere.append(this.getJoin());
		}
		
		StringBuffer strWhere = new StringBuffer();
		if(this.getWhere() != null && this.getWhere().trim().length() > 0) {
			strWhere.append(" WHERE ").append(this.getWhere());
		}
		
		if(this.getAnd() != null && this.getAnd().trim().length() > 0) {
			strWhere.append(this.getAnd());
		}
		
		if(this.getOr() != null && this.getOr().trim().length() > 0) {
			strWhere.append(this.getOr());
		}
		
		checkParams(this);
		strJoinWhere.append(strWhere);
		
		String countSQL = MessageFormat.format(countTemplateSQL, this.tableName, strJoinWhere.toString());
		
		return countSQL;
	}
	
	private <T extends Model> String builderPageSQL(T model, Connection conn) {
		if(this.sql != null && this.sql.trim().length() > 0) {
			return this.sql;
		}
		
		String strWhere = "";
		String strQuerySQL = "";
		String connection = conn.toString();
		
		Boolean isPage = (this.params.containsKey("pageIndex") && this.params.containsKey("pageSize")) || (this.params.containsKey("startIndex") && this.params.containsKey("endIndex")) || ((this.params.containsKey("offset") && this.params.containsKey("limit")));
		Boolean isMysql = connection.indexOf("jdbc:mysql") != -1;
		Boolean isOracle = connection.indexOf("jdbc:oracle") != -1;
		
		if(this.strColumns == null || this.strColumns.trim().length() == 0) 
			this.setColumns(model.strColumns);
		
		if(this.tableName == null || this.tableName.trim().length() == 0) 
			this.setTableName(model.getTableName());
		
		StringBuffer sbJoinWhere = new StringBuffer();
		if(this.getJoin() != null && this.getJoin().trim().length() > 0) {
			sbJoinWhere.append(this.getJoin());
		}
		
		StringBuffer sbWhere = new StringBuffer();
		if(this.getWhere() != null && this.getWhere().trim().length() > 0) {
			sbWhere.append(" WHERE ").append(this.getWhere());
		}
		
		if(this.getAnd() != null && this.getAnd().trim().length() > 0) {
			sbWhere.append(this.getAnd());
		}
		
		if(this.getOr() != null && this.getOr().trim().length() > 0) {
			sbWhere.append(this.getOr());
		}
		
		if(this.getOrderBy() != null && this.getOrderBy().trim().length() > 0) {
			sbWhere.append(this.getOrderBy());
		}
		
		if(isMysql) {
			if(this.params.containsKey("pageIndex") && this.params.containsKey("pageSize")) {
				Integer pageIndex = Integer.parseInt(this.params.get("pageIndex").toString());
				Integer pageSize = Integer.parseInt(this.params.get("pageSize").toString());
				
				this.offset = (pageIndex-1) * pageSize;
				this.limit = pageSize;
				
				this.params.remove("pageIndex");
				this.params.remove("pageSize");
				this.params.put("offset", this.offset);
				this.params.put("limit", this.limit);
				sbWhere.append(" LIMIT :offset,:limit ");
			} else if(this.params.containsKey("offset") && this.params.containsKey("limit")) {
				sbWhere.append(" LIMIT :offset,:limit ");
			} else if(this.offset != null && this.limit != null && this.limit > 0) {
				sbWhere.append(" LIMIT :offset,:limit ");
				this.params.put("offset", this.offset);
				this.params.put("limit", this.limit);
			}
		}
		
		strWhere = sbJoinWhere.append(sbWhere).toString();
		
		if(isOracle && isPage) {
			if(this.params.containsKey("pageIndex") && this.params.containsKey("pageSize")) {
				Integer pageIndex = Integer.parseInt(this.params.get("pageIndex").toString());
				Integer pageSize = Integer.parseInt(this.params.get("pageSize").toString());
				
				Integer startIndex = (pageIndex-1) * pageSize;
				Integer endIndex = pageIndex*pageSize;
				
				this.params.remove("pageIndex");
				this.params.remove("pageSize");
				this.params.put("startIndex", startIndex);
				this.params.put("endIndex", endIndex);
			} 
		}
		
		if(!isPage || isMysql) {
			strQuerySQL = MessageFormat.format(selectTemplateSQL, strColumns, this.tableName, strWhere); 
		}
		else if(isPage && isOracle) {
			String strInnerSQL = MessageFormat.format(selectTemplateSQL, strColumns, this.tableName, strWhere); 
			strQuerySQL = MessageFormat.format(selectPageForOracleSQL, strColumns, strInnerSQL); 
		}
		
		checkParams(this);
		
		return strQuerySQL;
	}
	
	private static Connection getConnection() {
		/*Connection conn = null;
        try {
        	conn = ConnectionManager.currentConnection();
        } catch (SQLException e) {
            throw new RuntimeException("sql exception :", e);
        }
        
		return  conn;*/
		
		DataSource ds = SpringContextUtil.getBean("dataSource", DataSource.class);
		Connection conn = DataSourceUtils.getConnection(ds);
		
		return conn;
	}
	
	/*public static void BeginTransaction() {
        try {
        	ConnectionManager.beginTransaction();
        } catch (SQLException e) {
            throw new RuntimeException("sql exception :", e);
        }
    }*/
	
	/*public static void Commit() {
        Connection conn = null;
        try {
        	conn = getConnection();
        	if(conn == null) return;
        	conn.commit();
        } catch (SQLException e) {
            throw new RuntimeException("sql exception :", e);
        }
        finally {
        	try {
				if(conn != null && !conn.isClosed()) {
					conn.close();
				}
			} catch (SQLException e) {
				throw new RuntimeException("sql exception :", e);
			}
        }
    }*/
	
	/*public static void Rollback() {
        Connection conn = null;
        try {
        	conn = getConnection();
        	if(conn == null) return;
        	conn.rollback();
        } catch (SQLException e) {
            throw new RuntimeException("sql exception :", e);
        }
        finally {
        	try {
				if(conn != null && !conn.isClosed()) {
					conn.close();
				}
			} catch (SQLException e) {
				throw new RuntimeException("sql exception :", e);
			}
        }
    }*/
	
	/*private static Boolean isAutoCommit() {
		return LocalConnetionManager.getHandConnection() == null && LocalConnetionManager.getAutoConnection() == null;
	}*/
	
	public Model set(String key, Object value) {
		this.put(key, value);
		return this;
	}
	
	private static void checkParams(Model model) {
		if(model.getTableName() == null || model.getTableName().trim().length() == 0) {
			throw new RuntimeException("table name can't be empty in model "+model.getClass().getName()+"!");
		}
		else if(model.columns.size() == 0) {
			throw new RuntimeException("column name can't be empty in model "+model.getClass().getName()+"!");
		}
		else if(model.primaryKey == null || model.primaryKey.trim().length() == 0) {
			throw new RuntimeException("primary key can't be empty in model "+model.getClass().getName()+"!");
		}
	}
	
	/*public static Model SelectDB(String dataSource) {
		ConnectionManager.setCurrentDataSource(dataSource); 
		return new Model();
	}*/
}
